*************************************************************************
* Table A.15: Decomposition of ration card deletions
* This table reports the decomposition of ration card deletions by Aadhaar seeding statu
********************************************************************************


************************************************************
*Survey Data 
************************************************************

use  		"${SurveyDataDir}/deletions_svy.dta", clear

gen   		unseeded  =  (seed_ind != 1)
gen  		ghost  	  =  (ghost_final == 1)


count
global T=r(N)

count if ghost==1
global T_G=r(N)

count if ghost==0
global T_NG=r(N)

svyset uid [pweight=pweight], strata(strata)



*********  overall ************* 
svy: tabulate deleted,count
matrix A=$T*e(Prop)

matrix list A
global  deleted_svy = round(A[1,1])   //deleted=0
global und_svy =round(A[2,1])    //deleted=1


********* by ghost and seeded ********* 


*among ghosts
svy: tabulate deleted unseeded if ghost==1
matrix G=$T_G*e(Prop)
matrix list G

global   und_sd_gh_svy=round(G[1,1])
global   und_un_gh_svy=round(G[1,2]) 
global   del_sd_gh_svy=round(G[2,1])
global   del_un_gh_svy=round(G[2,2])

di $und_sd_gh_svy 
di $und_un_gh_svy 
di $del_sd_gh_svy 
di $del_un_gh_svy
di $deleted_svy
di $und_svy


*among non ghosts
	
svy: tabulate deleted unseeded if ghost==0
matrix NG=$T_NG*e(Prop)
matrix list NG

global   und_sd_ng_svy=round(NG[1,1]) 
global   und_un_ng_svy=round(NG[1,2])  
global   del_sd_ng_svy=round(NG[2,1])
global   del_un_ng_svy=round(NG[2,2])

di $und_sd_ng_svy 
di $und_un_ng_svy 
di $del_sd_ng_svy 
di $del_un_ng_svy


************************************************************
*Admin Data 
************************************************************

use  		"${AdminDataDir}/deletions_admin.dta", clear

gen   		unseeded  =  (seed_ind != 1)

count if 	deleted == 1
global  	del_adm = `r(N)'

count if  	deleted == 0
global 		und_adm = `r(N)'

count if  	deleted == 1 & unseeded == 1
global  	del_un_adm = `r(N)'

count if  	deleted == 1 & unseeded == 0 
global  	del_sd_adm = `r(N)'

count if  	deleted == 0 & unseeded == 1
global  	und_un_adm = `r(N)'

count if  	deleted == 0 & unseeded == 0 
global  	und_sd_adm = `r(N)'

global 		ov_tot_adm = $del_sd_adm + $del_un_adm + $und_sd_adm + $und_un_adm
global  	ov_tot_svy = $del_sd_gh_svy + $del_un_gh_svy + ///
						 $del_sd_ng_svy + $del_un_ng_svy + ///
						 $und_sd_gh_svy + $und_un_gh_svy + ///
						 $und_sd_ng_svy + $und_un_ng_svy

						 
************************************************************
*Constructing table
************************************************************
gen  		d 		= ""
gen  		u 		= ""
gen 		t  		= ""
gen 		pc  	= ""
gen  		rowname = ""

replace     rowname = "Admin data"  					in 4

* Row 2 is unseeded (admin)
replace     rowname = "Unseeded"  						in 5
replace  	d  		= "$del_un_adm"   					in 5
replace  	u  		= "$und_un_adm"   					in 5

local 		tot  	= $del_un_adm + $und_un_adm
replace  	t  		= "`tot'"  							in 5

local  		frac    = 100*`tot'/$ov_tot_adm
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 5

* Row 3 is seeded (admin)
replace     rowname = "Seeded"  						in 6
replace  	d  		= "$del_sd_adm"   					in 6
replace  	u  		= "$und_sd_adm"   					in 6

local 		tot  	= $del_sd_adm + $und_sd_adm
replace  	t  		= "`tot'"  							in 6

local  		frac    = 100*`tot'/$ov_tot_adm
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 6

* Row 4 is total (admin)
replace     rowname = "Total" 	 						in 7

local 		tot  	= $del_sd_adm + $del_un_adm
replace  	d  		= "`tot'" 		  					in 7

local 		tot  	= $und_sd_adm + $und_un_adm
replace  	u  		= "`tot'" 		   					in 7

local 		tot  	= $del_sd_adm + $del_un_adm + ///
					  $und_sd_adm + $und_un_adm
replace  	t  		= "`tot'"  							in 7

replace     pc  	= "100%"  						    in 7

* Row 5 is percent of total (admin)
replace     rowname = "% of overall total" 	 			in 8

local 		tot  	= $del_sd_adm + $del_un_adm
local  		frac    = 100*`tot'/$ov_tot_adm
local  		pc  	= round(`frac', 0.1)
replace     d  	    = "`pc'%"  						    in 8

local 		tot  	= $und_sd_adm + $und_un_adm
local  		frac    = 100*`tot'/$ov_tot_adm
local  		pc  	= round(`frac', 0.1)
replace     u  	    = "`pc'%"  						    in 8

replace  	t  		= "100%" 							in 8 			

replace     rowname = "Survey data"  					in 9

*********************** survey **********************************************
* Row 7 is unseeded and ghost (survey)
replace     rowname = "Unseeded and ghost"  			in 10
replace  	d  		= "$del_un_gh_svy" 					in 10
replace  	u  		= "$und_un_gh_svy"   				in 10

local 		tot  	= $del_un_gh_svy + $und_un_gh_svy
replace  	t  		= "`tot'"  							in 10

local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 10

* Row 8 is unseeded and not ghost (survey)
replace     rowname = "Unseeded and not ghost"  	    in 11
replace  	d  		= "$del_un_ng_svy" 					in 11
replace  	u  		= "$und_un_ng_svy"   				in 11

local 		tot  	= $del_un_ng_svy + $und_un_ng_svy
replace  	t  		= "`tot'"  							in 11

local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 11

* Row 9 is seeded and ghost (survey)
replace     rowname = "Seeded and ghost"  	            in 12
replace  	d  		= "$del_sd_gh_svy" 					in 12
replace  	u  		= "$und_sd_gh_svy"   				in 12

local 		tot  	= $del_sd_gh_svy + $und_sd_gh_svy
replace  	t  		= "`tot'"  							in 12

local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 12

* Row 10 is seeded and not ghost (survey)
replace     rowname = "Seeded and not ghost"  	        in 13
replace  	d  		= "$del_sd_ng_svy" 					in 13
replace  	u  		= "$und_sd_ng_svy"   				in 13

local 		tot  	= $del_sd_ng_svy + $und_sd_ng_svy
replace  	t  		= "`tot'"  							in 13

local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     pc  	= "`pc'%"  						    in 13

* Row 11 is total (survey)
replace     rowname = "Total" 	 						in 14

local 		tot  	= $del_un_gh_svy + $del_un_ng_svy + ///
					  $del_sd_gh_svy + $del_sd_ng_svy
replace  	d  		= "`tot'" 		  					in 14

local 		tot  	= $und_un_gh_svy + $und_un_ng_svy + ///
					  $und_sd_gh_svy + $und_sd_ng_svy
replace  	u  		= "`tot'" 		   					in 14

local 		tot  	= $und_un_gh_svy + $und_un_ng_svy + ///
					  $und_sd_gh_svy + $und_sd_ng_svy + ///
					  $del_un_gh_svy + $del_un_ng_svy + ///
					  $del_sd_gh_svy + $del_sd_ng_svy
replace  	t  		= "`tot'"  							in 14

replace     pc  	= "100%"  						    in 14


* Row 12 is percent of total (admin)
replace     rowname = "% of overall total" 	 			in 15

local 		tot  	= $del_un_gh_svy + $del_un_ng_svy + ///
					  $del_sd_gh_svy + $del_sd_ng_svy
local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     d  	    = "`pc'%"  						    in 15

local 		tot  	= $und_un_gh_svy + $und_un_ng_svy + ///
					  $und_sd_gh_svy + $und_sd_ng_svy
local  		frac    = 100*`tot'/$ov_tot_svy
local  		pc  	= round(`frac', 0.1)
replace     u  	    = "`pc'%"  						    in 15

replace  	t  		= "100%" 							in 15 	



* Keeping only relevant rows columns
keep 		rowname d u t pc
keep if 	rowname != ""

order       rowname d u t pc  

* Fixing some format issues
replace 	pc = "8.7%"  if pc == "8.700000000000001%"
replace 	pc = "93.1%" if pc == "91.30000000000001%"
replace 	u  = "94.1%" if u  == "94.10000000000001%"
replace 	pc = "0.6%"  if pc == ".6000000000000001%"
replace 	pc = "5.8%"  if pc == "5.800000000000001%"
replace 	pc = "0.7%"  if pc == ".7000000000000001%"
replace 	pc = "92.8%" if pc == "92.80000000000001%"
replace 	d  = "90.1"  if d  == "90.10000000000001"
replace 	u  = "5.6"  if u  == "5.600000000000001"
replace 	u  = "94.6%"  if u  == "94.60000000000001%"


* Adding comma separators
forval 		i  = 2/4 {
foreach   var in d u t {
	
	replace `var' = substr(strreverse(`var'), 1, 3) + "," + trim(substr(strreverse(`var'), 4, 20)) in `i'
	replace `var' = strreverse(`var') in `i'
	
	}
	}
	
forval 		i  = 3/4 {
foreach   var in u t {
	
	replace `var' = substr(strreverse(`var'), 1, 7) + "," + trim(substr(strreverse(`var'), 8, 20)) in `i'
	replace `var' = strreverse(`var') in `i'
	
	}
	}

	
#delimit ;

texsave  rowname d u t pc using "${OutputDir}/Table8.tex",  
		 headerlines("& Deleted & Non-deleted & Total & \%" 
					 "\cmidrule(lr){2-2} \cmidrule(lr){3-3} \cmidrule(lr){4-4} \cmidrule(lr){5-5}" 
					 "& (1) & (2) & (3) & (4)")
		 hlines(3 5 10) nonames frag replace 
		 location(htbp) 
		 marker(tab:deletions)  title(Decomposition of ration card deletions) 
		 slanted("Admin data" "Survey data") 
		 footnote("This table reports the decomposition of ration card deletions by Aadhaar seeding status. The top panel ``Admin data'' shows the results from the universe of ration cards in our 10 study districts, and the bottom panel ``Suryey data'' shows results from our sampled ration cards, for which we show counts adjusted by sampling probability and categorized beneficiaries as ``ghosts'' based on survey results. A ration card is ``Deleted'' if it was present in the beneficiary list in October 2016 but absent in May 2018, and is ``Non-deleted'' if still present in May 2018. A ration card is ``Unseeded'' if it did not have any Aadhaar number seeded to the card in October 2016, and ``Seeded'' if it did.", 
		 size(footnotesize))
 ;
#delimit cr
		 
		 
		 
		 
